﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace 图书管理系统
{
    public partial class 个人中心 : Form
    {

        public 个人中心()
        {
            InitializeComponent();
        }

        private void button2_Click(object sender, EventArgs e)
        {

        }

        private void button5_Click(object sender, EventArgs e)
        {

        }

        private void tabPage1_Click(object sender, EventArgs e)
        {

        }

        private void 个人中心_Load(object sender, EventArgs e)
        {
            User user = 用户登录.user;
            String id = user.Id;
            String identy = user.Identy;
            SqlConnection conn = new SqlConnection();
            SqlConnection conn1 = new SqlConnection();
            SqlConnection conn2 = new SqlConnection();
            SqlConnection conn3 = new SqlConnection();
            SqlConnection conn4 = new SqlConnection();
            SqlConnection conn5 = new SqlConnection();
            SqlConnection conn6 = new SqlConnection();
            SqlConnection conn7 = new SqlConnection();
            SqlConnection conn8 = new SqlConnection();
            SqlConnection conn9 = new SqlConnection();
            SqlConnection conn10 = new SqlConnection();
            SqlConnection conn11 = new SqlConnection();
            SqlConnection conn12 = new SqlConnection();
            SqlConnection conn13 = new SqlConnection();
            SqlConnection conn14 = new SqlConnection();
            SqlConnection conn15 = new SqlConnection();
            SqlConnection conn16 = new SqlConnection();
            try
            {
                //获取提示信息
                SqlConnection conn18 = new SqlConnection();
                string sql18 = "select * from Appointmentrecord_Table where ReaderID='" + id.ToString() + "'and OrderState='预约中' and Tips!='null'";
                SqlCommand cmd18 = Dao.SqlConnect(sql18, conn18);
                DataTable ds18 = new DataTable();
                SqlDataAdapter sqlDataAdapter18 = new SqlDataAdapter(cmd18);
                conn18.Open();
                sqlDataAdapter18.Fill(ds18);
                foreach (DataRow dataRow in ds18.Rows)
                {
                    int index18 = dataGridView8.Rows.Add();
                    dataGridView8.Rows[index18].Cells[2].Value = dataRow["Tips"].ToString();
                    dataGridView8.Rows[index18].Cells[0].Value = dataRow["BookNum"].ToString();
                    String BookNum = dataRow["BookNum"].ToString();

                    string sql11 = "select BookName from Binfo_Table where BookNum='" + BookNum.ToString() + "'";
                    SqlCommand cmd11 = Dao.SqlConnect(sql11, conn11);
                    conn11.Open();

                    SqlDataReader reader11 = cmd11.ExecuteReader();
                    String bookname2 = null;
                    while (reader11.Read())
                    {
                        bookname2 = reader11[0].ToString();
                    }
                    dataGridView8.Rows[index18].Cells[1].Value = bookname2;
                    conn11.Close();
                }

                //赔书申请查询
                string sql14 = "select * from Claimrecord_Table where ReaderID='" + id.ToString() + "'";
                SqlCommand cmd14 = Dao.SqlConnect(sql14, conn14);
                DataTable ds14 = new DataTable();
                SqlDataAdapter sqlDataAdapter14 = new SqlDataAdapter(cmd14);
                conn14.Open();
                sqlDataAdapter14.Fill(ds14);
                foreach (DataRow dataRow in ds14.Rows)
                {
                    int index14 = dataGridView7.Rows.Add();
                    dataGridView7.Rows[index14].Cells[0].Value = dataRow["ReplyID"].ToString();
                    dataGridView7.Rows[index14].Cells[1].Value = dataRow["BookNum"].ToString();
                    dataGridView7.Rows[index14].Cells[3].Value = dataRow["Reply_time"].ToString();
                    dataGridView7.Rows[index14].Cells[4].Value = dataRow["ReplyState"].ToString();
                    //dataGridView7.Rows[index14].Cells[4].Value = dataRow["FineStyle"].ToString();

                    String BookNum = dataRow["BookNum"].ToString();
                    string sql15 = "select BookName from Binfo_Table where BookNum='" + BookNum.ToString() + "'";
                    SqlCommand cmd15 = Dao.SqlConnect(sql15, conn15);
                    conn15.Open();

                    SqlDataReader reader15 = cmd15.ExecuteReader();
                    String bookname15 = null;
                    while (reader15.Read())
                    {
                        bookname15 = reader15[0].ToString();
                    }
                    // String bookname = cmd7.ExecuteScalar().ToString();
                    dataGridView7.Rows[index14].Cells[2].Value = bookname15;
                    conn15.Close();



                }

                //查询未交罚款记录
                string sql12 = "select * from Penaltyrecord_Table where ReaderID='" + id.ToString() + "'and FineState='未缴费'";
                SqlCommand cmd12 = Dao.SqlConnect(sql12, conn12);
                DataTable ds12 = new DataTable();
                SqlDataAdapter sqlDataAdapter12 = new SqlDataAdapter(cmd12);
                conn12.Open();
                sqlDataAdapter12.Fill(ds12);
                foreach (DataRow dataRow in ds12.Rows)
                {
                    int index12 = dataGridView6.Rows.Add();
                    dataGridView6.Rows[index12].Cells[0].Value = dataRow["FineID"].ToString();
                    dataGridView6.Rows[index12].Cells[1].Value = dataRow["FineMoney"].ToString();
                    dataGridView6.Rows[index12].Cells[2].Value = dataRow["Generate_time"].ToString();
                    dataGridView6.Rows[index12].Cells[3].Value = dataRow["FineState"].ToString();
                    dataGridView6.Rows[index12].Cells[4].Value = dataRow["FineStyle"].ToString();

                    string sql13 = "select Sum(FineMoney) from Penaltyrecord_Table where ReaderID='" + id.ToString() + "'and FineState='未缴费'";
                    SqlCommand cmd13 = Dao.SqlConnect(sql13, conn13);
                    conn13.Open();
                    SqlDataReader reader13 = cmd13.ExecuteReader();
                    String sum = null;
                    while (reader13.Read())
                    {
                        sum = reader13[0].ToString();
                    }
                    label18.Text = sum;
                    conn13.Close();

                }

                //查询用户个人信息               
                string sql = "select * from Readerinfo where ReaderID='" + id.ToString() + "'";
                SqlCommand cmd = Dao.SqlConnect(sql, conn);
                DataSet dt = new DataSet();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                conn.Open();
                sqlDataAdapter.Fill(dt, "Readerinfo");
                label2.Text = dt.Tables["Readerinfo"].Rows[0]["ReaderName"].ToString();
                label4.Text = dt.Tables["Readerinfo"].Rows[0]["Major"].ToString();
                label6.Text = dt.Tables["Readerinfo"].Rows[0]["ReaderTel"].ToString();
                label7.Text = dt.Tables["Readerinfo"].Rows[0]["Identy"].ToString();

                //查询预约记录
                string sql1 = "select * from Appointmentrecord_Table where ReaderID='" + id.ToString() + "' and OrderState='已完成' or OrderState='已取消'";
                SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
                DataTable ds = new DataTable();
                SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(cmd1);
                conn1.Open();
                sqlDataAdapter1.Fill(ds);
                foreach (DataRow dataRow in ds.Rows)
                {
                    int index = dataGridView1.Rows.Add();
                    dataGridView1.Rows[index].Cells[0].Value = dataRow["OrderID"].ToString();
                    dataGridView1.Rows[index].Cells[2].Value = dataRow["BookNum"].ToString();
                    dataGridView1.Rows[index].Cells[3].Value = dataRow["Order_time"].ToString();
                    dataGridView1.Rows[index].Cells[4].Value = dataRow["OrderState"].ToString();
                    String BookNum = dataRow["BookNum"].ToString();
                   // MessageBox.Show(BookNum);
                    string sql5 = "select BookName from Binfo_Table where BookNum='" + BookNum.ToString() + "'";
                    SqlCommand cmd5 = Dao.SqlConnect(sql5, conn5);
                    conn5.Open();
                    SqlDataReader reader = cmd5.ExecuteReader();
                    String bookname1 = null;
                    while (reader.Read())
                    {
                        bookname1 = reader[0].ToString();
                    }
                    dataGridView1.Rows[index].Cells[1].Value = bookname1;
                    conn5.Close();
                }
                //查询正在预约的图书
                string sql10 = "select * from Appointmentrecord_Table where ReaderID='" + id.ToString() + "'and OrderState='预约中'";
                SqlCommand cmd10 = Dao.SqlConnect(sql10, conn10);
                DataTable ds10 = new DataTable();
                SqlDataAdapter sqlDataAdapter10 = new SqlDataAdapter(cmd10);
                conn10.Open();
                sqlDataAdapter10.Fill(ds10);
                foreach (DataRow dataRow in ds10.Rows)
                {
                    int index = dataGridView5.Rows.Add();
                    dataGridView5.Rows[index].Cells[0].Value = dataRow["OrderID"].ToString();
                    dataGridView5.Rows[index].Cells[2].Value = dataRow["BookNum"].ToString();
                    dataGridView5.Rows[index].Cells[3].Value = dataRow["Order_time"].ToString();
                    //获取提示信息
                    int indextip = dataGridView8.Rows.Add();
                    
                    String BookNum = dataRow["BookNum"].ToString();
                    
                    string sql11 = "select BookName from Binfo_Table where BookNum='" + BookNum.ToString() + "'";
                    SqlCommand cmd11 = Dao.SqlConnect(sql11, conn11);
                    conn11.Open();

                    SqlDataReader reader11 = cmd11.ExecuteReader();
                    String bookname2 = null;
                    while (reader11.Read())
                    {
                        bookname2 = reader11[0].ToString();
                    }
                    dataGridView5.Rows[index].Cells[1].Value = bookname2;
                    conn11.Close();
                    //查询当前排位
                    SqlConnection conn17 = new SqlConnection();
                    String time = dataRow["Order_Time"].ToString();
                    string sql17 = "select count(*) From Appointmentrecord_Table where  OrderState='预约中' and BookNum='" + BookNum.ToString() + "' and Order_time<'" + time + "'";
                    SqlCommand cmd17 = Dao.SqlConnect(sql17, conn17);
                    conn17.Open();
                    SqlDataReader reader17 = cmd17.ExecuteReader();
                    String count = null;
                    while (reader17.Read())
                    {
                        count = reader17[0].ToString();
                    }
                    int c = Convert.ToInt32(count) + 1;
                    dataGridView5.Rows[index].Cells[4].Value = c;
                    conn17.Close();
                }

                //正在借阅书籍
                string sql6 = "select * from BorrowinfoTable where ReaderID='" + id.ToString() + "'and BorrowState='借阅中'";
                SqlCommand cmd6 = Dao.SqlConnect(sql6, conn6);
                DataTable ds6 = new DataTable();
                SqlDataAdapter sqlDataAdapter6 = new SqlDataAdapter(cmd6);
                conn6.Open();
                sqlDataAdapter6.Fill(ds6);
                foreach (DataRow dataRow in ds6.Rows)
                {
                    int index6 = dataGridView4.Rows.Add();
                    dataGridView4.Rows[index6].Cells[0].Value = dataRow["BorrowID"].ToString();
                    dataGridView4.Rows[index6].Cells[1].Value = dataRow["BookNum"].ToString();
                    dataGridView4.Rows[index6].Cells[3].Value = dataRow["Borrow_time"].ToString();
                    dataGridView4.Rows[index6].Cells[4].Value = dataRow["Return_time"].ToString();
                    dataGridView4.Rows[index6].Cells[5].Value = dataRow["BorrowState"].ToString();
                    String BookNum = dataRow["BookNum"].ToString();
                    string sql7 = "select BookName from Binfo_Table where BookNum='" + BookNum.ToString() + "'";
                    SqlCommand cmd7 = Dao.SqlConnect(sql7, conn7);
                    conn7.Open();

                    SqlDataReader reader7 = cmd7.ExecuteReader();
                    String bookname7 = null;
                    while (reader7.Read())
                    {
                        bookname7 = reader7[0].ToString();
                    }
                   // String bookname = cmd7.ExecuteScalar().ToString();
                    dataGridView4.Rows[index6].Cells[2].Value = bookname7;
                    conn7.Close();

                }

                //查询借阅记录
                string sql2 = "select * from BorrowinfoTable where ReaderID='" + id.ToString() + "'and BorrowState='已归还'";
                SqlCommand cmd2 = Dao.SqlConnect(sql2, conn2);
                DataTable ds2 = new DataTable();
                SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter(cmd2);
                conn2.Open();
                sqlDataAdapter2.Fill(ds2);
                foreach (DataRow dataRow in ds2.Rows)
                {
                    int index2 = dataGridView2.Rows.Add();
                    dataGridView2.Rows[index2].Cells[0].Value = dataRow["BorrowID"].ToString();
                    dataGridView2.Rows[index2].Cells[1].Value = dataRow["BookNum"].ToString();
                    dataGridView2.Rows[index2].Cells[3].Value = dataRow["Borrow_time"].ToString();
                    dataGridView2.Rows[index2].Cells[4].Value = dataRow["Return_time"].ToString();
                    dataGridView2.Rows[index2].Cells[5].Value = dataRow["BorrowState"].ToString();
                    String BookNum= dataRow["BookNum"].ToString();
                    string sql3 = "select BookName from Binfo_Table where BookNum='"+BookNum.ToString()+"'";
                    SqlCommand cmd3 = Dao.SqlConnect(sql3, conn3);
                    conn3.Open();

                    SqlDataReader reader3 = cmd3.ExecuteReader();
                    String bookname3 = null;
                    while (reader3.Read())
                    {
                        bookname3 = reader3[0].ToString();
                    }
                    //String bookname = cmd3.ExecuteScalar().ToString();
                    dataGridView2.Rows[index2].Cells[2].Value =bookname3;
                    conn3.Close();
                }

                //查询荐购记录
                string sql4 = "select * from Purchaserecord_Table where ReaderID='" + id.ToString() + "'";
                SqlCommand cmd4 = Dao.SqlConnect(sql4, conn4);
                DataTable ds4 = new DataTable();
                SqlDataAdapter sqlDataAdapter4 = new SqlDataAdapter(cmd4);
                conn4.Open();
                sqlDataAdapter4.Fill(ds4);
                foreach (DataRow dataRow4 in ds4.Rows)
                {
                    int index4 = dataGridView3.Rows.Add();
                    dataGridView3.Rows[index4].Cells[0].Value = dataRow4["RecmdatID"].ToString();
                    dataGridView3.Rows[index4].Cells[1].Value = dataRow4["RecmdatBook"].ToString();
                    dataGridView3.Rows[index4].Cells[2].Value = dataRow4["Author"].ToString();
                    dataGridView3.Rows[index4].Cells[3].Value = dataRow4["Publication_time"].ToString();
                    dataGridView3.Rows[index4].Cells[4].Value = dataRow4["ISBN"].ToString();
                    dataGridView3.Rows[index4].Cells[6].Value = dataRow4["Note"].ToString();
                    dataGridView3.Rows[index4].Cells[5].Value = dataRow4["Recmdat"].ToString();
                    
                }

                
                //查询借阅规则
                string sql16 = "select * from Borrowingrules_Table where Identy='" + identy.ToString()+"'";
                SqlCommand cmd16 = Dao.SqlConnect(sql16, conn16);
                conn16.Open();

                SqlDataReader reader16 = cmd16.ExecuteReader();
                String bnum = null;
                String onum = null;
                while (reader16.Read())
                {
                    bnum = reader16[0].ToString();
                    onum = reader16[1].ToString();
                }
                int b = Convert.ToInt32(bnum);//8
                int o = Convert.ToInt32(onum);//10


                //查询预约数量
                string sql8 = "select count(*) from Appointmentrecord_Table where ReaderID='" + id.ToString() + "' and OrderState='预约中'";
                SqlCommand cmd8 = Dao.SqlConnect(sql8, conn8);
                conn8.Open();
                int val_count = (int)cmd8.ExecuteScalar();
                int real_val_count = o - val_count;
                label13.Text = Convert.ToString(val_count);
                label15.Text = Convert.ToString(real_val_count);
                conn8.Close();

                //查询借阅数量
                string sql9 = "select count(*) from BorrowinfoTable where ReaderID='" + id.ToString() + "' and BorrowState='借阅中'";
                SqlCommand cmd9 = Dao.SqlConnect(sql9, conn9);
                conn9.Open();
                int val_count1 = (int)cmd9.ExecuteScalar();               
                int real_val_count1 = b - val_count1;
                label11.Text = Convert.ToString(real_val_count1);
                label9.Text = Convert.ToString(val_count1);
                conn9.Close();

                

            }
            catch (Exception ex) 
            {
                MessageBox.Show(ex.Message);
            }
            finally {
                conn.Close();
                conn1.Close();
                conn2.Close();                
                conn4.Close();
                conn10.Close();
                conn12.Close();
                conn14.Close();
            }
        }

        private void tabPage2_Click(object sender, EventArgs e)
        {

        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void dataGridView5_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if(dataGridView5.Columns[e.ColumnIndex].Name == "Column32")
            {
                String ns = dataGridView5.CurrentRow.Cells[2].Value.ToString();
                //MessageBox.Show(ns);
                User user = 用户登录.user;
                String id = user.Id;
               // MessageBox.Show(id);
                SqlConnection conn = new SqlConnection();
                try {
                    string sql = "update Appointmentrecord_Table set OrderState='已取消' where BookNum='"+ns+"' and ReaderID='"+id.ToString()+"'";                    
                    SqlCommand cmd = Dao.SqlConnect(sql, conn);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("取消预约成功");
                    this.Hide();
                    个人中心 f1 = new 个人中心();
                    f1.ShowDialog();
                    this.Close();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally {
                    conn.Close();
                }


            }
        }

        private void tabControl1_Click(object sender, EventArgs e)
        {
/*            this.Hide();
            个人中心 f1 = new 个人中心();
            f1.ShowDialog();
            this.Close();*/
        }

        private void button3_Click(object sender, EventArgs e)
        {
            User user = 用户登录.user;
            String id = user.Id;
            SqlConnection conn = new SqlConnection();
            if(label18.Text!="0")
            {
                try
                {
                    string sql = "update Penaltyrecord_Table set FineState='已缴费' where ReaderID='" + id.ToString() + "'";
                    SqlCommand cmd = Dao.SqlConnect(sql, conn);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("缴纳罚款成功");
                    this.Hide();
                    个人中心 f1 = new 个人中心();
                    f1.ShowDialog();
                    this.Close();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                MessageBox.Show("未存在欠费记录！");
            }
           
        }

        private void button5_Click_1(object sender, EventArgs e)
        {
            User user = 用户登录.user;
            this.Hide();
            首页 form = new 首页(user);
            form.Show();
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            this.Close();
            还书 form2 = new 还书();
            form2.Show();
           
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
            荐购 form2 = new 荐购();
            form2.Show();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            this.Close();
            赔书 form2 = new 赔书();
            form2.Show();
        }
    }
}
